﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Ylh.DBUtility;
using Ylh.IDAL;
using Ylh.Model;
using System.Data;
using System.Data.SqlClient;

namespace Ylh.SQLServerDAL
{
    public class StaffDAL : IStaff
    {
        public DataTable IsLogin(string ConStr, string LoginName, string Pwd)
        {
            string sql = @"select * FROM TbStaff  WHERE StaffLoginName=@LoginName AND StaffPwd=@StaffPwd and IsDeparture='0'";

            SqlParameter[] pre = new SqlParameter[] 
            {
              new SqlParameter ("@LoginName",LoginName),
              new SqlParameter ("@StaffPwd",Pwd)
             };

            return SqlHelper.ExcuteQuery(ConStr, sql, pre, CommandType.Text);
        }

        public int InsertInStaff(string ConStr, Ylh.Model.TbStaff model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TbStaff(");
            strSql.Append("StaffLoginName,StaffPwd,StaffName,DepartmentID,RoleID,IsDeparture)");
            strSql.Append(" values (");
            strSql.Append("@StaffLoginName,@StaffPwd,@StaffName,@DepartmentID,Null,@IsDeparture)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@StaffLoginName", SqlDbType.VarChar,20),
					new SqlParameter("@StaffPwd", SqlDbType.VarChar,100),
					new SqlParameter("@StaffName", SqlDbType.VarChar,20),
					new SqlParameter("@DepartmentID", SqlDbType.Int,4),
					new SqlParameter("@IsDeparture", SqlDbType.Bit,1)};
            parameters[0].Value = model.StaffLoginName;
            parameters[1].Value = model.StaffPwd;
            parameters[2].Value = model.StaffName;
            parameters[3].Value = model.DepartmentID;
            parameters[4].Value = model.IsDeparture;

            DataTable dt = SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            return Convert.ToInt32(dt.Rows[0][0]);
        }

        public bool UpdateStaff(string ConStr, string StaffName, string staffID, string DepartmentID)
        {

            string sql = @"update TbStaff set  StaffName=@StaffName,DepartmentID=@DepartmentID where StaffID=@StaffID ";

            SqlParameter[] pre = new SqlParameter[] 
            {
              new SqlParameter ("@DepartmentID",DepartmentID),
               new SqlParameter ("@StaffName",StaffName),
               new SqlParameter ("@StaffID",staffID)
             };

            int i = SqlHelper.ExecuteNonQuery(ConStr, sql, pre, CommandType.Text);

            if (i > 0) return true;
            return false;
        }



        public DataTable GetStaffData(string ConStr, string DepartmentID)
        {
            string sql = @"select * FROM TbStaff left join TbStaffInfo on TbStaff.StaffID=TbStaffInfo.StaffID
WHERE DepartmentID=" + DepartmentID;

            return SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text);
        }



        /// <summary>
        /// 根据员工编号删除员工信息
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="StaffID"></param>
        /// <returns></returns>
        public bool Delete(string ConStr, int StaffID)
        {
            string sql = @"DELETE TbStaffInfo WHERE StaffID =" + StaffID + @" 
                            DELETE TbStaff where  StaffID=" + StaffID;

            int i = SqlHelper.ExecuteNonQuery(ConStr, sql.ToString(), CommandType.Text);

            if (i > 0) return true;
            return false;

        }
        /// <summary>
        /// 根据员工编号获得员工信息
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="StaffID"></param>
        /// <returns></returns>
        public DataTable GetStaff(string ConStr, int StaffID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT * FROM TbStaff 
LEFT JOIN TbRole on TbRole.RoleID=TbStaff.RoleID
LEFT join TbStaffInfo on TbStaffInfo.StaffID=TbStaff.StaffID
LEFT JOIN TbDepartment on TbDepartment.DepartmentID=TbStaff.DepartmentID");
            strSql.Append(" where TbStaff.StaffID=@StaffID ");
            SqlParameter[] parameters = {
					new SqlParameter("@StaffID", SqlDbType.Int,4)};
            parameters[0].Value = StaffID;

            Ylh.Model.TbStaff model = new Ylh.Model.TbStaff();
            return SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);
        }

        /// <summary>
        /// 判断员工下面是否有客户
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="StaffID"></param>
        /// <returns></returns>
        public bool hasCust(string ConStr, int StaffID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM TbStaff inner join TbCust on TbStaff.StaffID=TbCust.BusinessStaffID ");
            strSql.Append(" WHERE StaffID=@StaffID ");

            SqlParameter[] parameters = {
					new SqlParameter("@StaffID", SqlDbType.Int,4)};
            parameters[0].Value = StaffID;

            Ylh.Model.TbStaff model = new Ylh.Model.TbStaff();
            DataTable dt = SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);
            if (dt.Rows.Count > 0) return true;
            return false;
        }

        /// <summary>
        /// 更新员工的角色
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="StaffID">员工编号</param>
        /// <param name="RoleID">角色编号</param>
        /// <returns></returns>
        public bool UpdateStaffRole(string ConStr, int StaffID, string RoleID)
        {
            string sql = @"UPDATE TbStaff SET RoleID=@RoleID where StaffID=" + StaffID;
            SqlParameter[] pre = new SqlParameter[] 
            {
              new SqlParameter ("@RoleID",RoleID)
             };

            int i = SqlHelper.ExecuteNonQuery(ConStr, sql, pre, CommandType.Text);

            if (i > 0) return true;
            return false;
        }


        /// <summary>
        /// 更新员工是否离职
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="StaffID">员工编号</param>
        /// <param name="IsDeparture">是否离职</param>
        /// <returns></returns>
        public bool UpdateStaffDeparture(string ConStr, int StaffID, bool IsDeparture)
        {
            string sql = @"UPDATE TbStaff SET IsDeparture=@IsDeparture where StaffID=" + StaffID;
            SqlParameter[] pre = new SqlParameter[] 
            {
              new SqlParameter ("@IsDeparture",IsDeparture)
             };

            int i = SqlHelper.ExecuteNonQuery(ConStr, sql, pre, CommandType.Text);

            if (i > 0) return true;
            return false;
        }

        /// <summary>
        /// 更新员工密码
        /// </summary>
        /// <param name="StaffID">员工ID</param>
        /// <param name="oldPwd">旧密码</param>
        /// <param name="newPwd">新密码</param>
        /// <returns></returns>
        public int UpdateStaffPwd(string ConStr ,int StaffID, string oldPwd, string newPwd)
        {
            string sql1 = @"select * FROM TbStaff  WHERE StaffID=@StaffID AND StaffPwd=@StaffPwd";
            SqlParameter[] pre1 = new SqlParameter[]
            {
                new SqlParameter("@StaffID",StaffID),
                new SqlParameter("@StaffPwd",oldPwd)
            };
            if (SqlHelper.ExcuteQuery(ConStr,sql1,pre1,CommandType.Text).Rows.Count<=0)
            {
                return 0;
            }

            string sql2 = @"UPDATE TbStaff SET StaffPwd=@StaffPwd where StaffID=" + StaffID;
            SqlParameter[] pre2 = new SqlParameter[] 
            {
              new SqlParameter ("@StaffPwd",newPwd)
             };

            int i = SqlHelper.ExecuteNonQuery(ConStr, sql2, pre2, CommandType.Text);

            if (i > 0) return 1;
            return -1;
        }


        //重置员工的密码
        public int UpdateStaffPwd(string ConStr, int StaffID, string newPwd)
        {
            string sql2 = @"UPDATE TbStaff SET StaffPwd=@StaffPwd where StaffID=" + StaffID;
            SqlParameter[] pre2 = new SqlParameter[] 
            {
              new SqlParameter ("@StaffPwd",newPwd)
             };

            int i = SqlHelper.ExecuteNonQuery(ConStr, sql2, pre2, CommandType.Text);

            if (i > 0) return 1;
            return -1;
        }



    }
}
